﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;

namespace QuickExcel
{
    public partial class SelectByValue : Form
    {
        public SelectByValue()
        {
            InitializeComponent();
        }

        private void SelectByValue_Load(object sender, EventArgs e)
        {
            txtSourceRange.Text = Globals.ThisAddIn.Application.Selection.Address;
            comValues.Items.AddRange(new string[] { "Empty", "Between", "Not Between", "Equal to", "Not equal to", "Greater than", "Less than", "Greater than or equal to", "Less than or equal to", "Logical TRUE", "Logical FALSE", "Similar to pattern", "Not similar to pattern" });
            comValues.SelectedIndex = 0;
            txtFirstValue.Visible = txtSecondValue.Visible = lblAnd.Visible = txtValue.Visible = lblValue.Visible = false;
        }

        private void txtSourceRange_Clicked(object sender, EventArgs e)
        {
            Excel.Range rng;
            rng = Globals.ThisAddIn.Application.InputBox("Please select range", "Select Range", txtSourceRange.Text, this.Left - 5, this.Top + 5, Type: 8);
            rng.Select();
            txtSourceRange.Text = rng.Address;
            this.Activate();
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            Excel.Range rng;
            object[,] oRng;
            string sAddress;            
            bool bSelectCells, isBool;
            double val1, val2,dVal;
            string[] sCombineAdd;

            if (string.IsNullOrWhiteSpace(txtSourceRange.Text))
            {
                MessageBox.Show("Source range is empty");
                this.Activate();
                return;
            }

            bSelectCells = optSelectCells.Checked ? true : false;
            rng = Globals.ThisAddIn.Application.ActiveSheet.Range(txtSourceRange.Text);            
            sAddress=rng.Cells[1,1].Address;            

            switch(comValues.Text)
            {
                case "Empty": 
                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])))                            
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";                                                            
                        }
                    }       
                    break;

                case "Between":
                    if (string.IsNullOrWhiteSpace(txtFirstValue.Text) || string.IsNullOrWhiteSpace(txtSecondValue.Text))
                    {                        
                        MessageBox.Show("Please enter both values");                        
                        this.Activate();
                        return;
                    }

                    if (Convert.ToDouble(txtFirstValue.Text) < Convert.ToDouble(txtSecondValue.Text))
                    {
                        val1 = Convert.ToDouble(txtFirstValue.Text);
                        val2 = Convert.ToDouble(txtSecondValue.Text);
                    }
                    else
                    {
                        val2 = Convert.ToDouble(txtFirstValue.Text);
                        val1 = Convert.ToDouble(txtSecondValue.Text);
                    }

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                            {
                                if (dVal >= val1 && dVal <= val2)                                
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";                                
                            }
                        }
                    }                    
                    break;

                case "Not Between":
                    if (string.IsNullOrWhiteSpace(txtFirstValue.Text) || string.IsNullOrWhiteSpace(txtSecondValue.Text))
                    {                        
                        MessageBox.Show("Please enter both values");                        
                        this.Activate();
                        return;
                    }

                    val1 = Convert.ToDouble(txtFirstValue.Text);
                    val2 = Convert.ToDouble(txtSecondValue.Text);
                    
                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                            {
                                if (dVal <= val1 || dVal >= val2)                                
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Equal to":
                    if (string.IsNullOrEmpty(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrEmpty(Convert.ToString(oRng[i, j])))
                            {
                                if(double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                                {
                                    if(dVal==Convert.ToDouble(txtValue.Text))
                                        sAddress = sAddress + rng.Cells[i, j].Address + " ";
                                }
                                else if (Convert.ToString(oRng[i, j]).Equals(txtValue.Text))
                                {
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                                }
                            }
                        }
                    } 
                    break;

                case "Not equal to":
                    if (string.IsNullOrEmpty(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrEmpty(Convert.ToString(oRng[i, j])))
                            {
                                if (Convert.ToString(oRng[i, j]) != txtValue.Text)
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Greater than":

                    if (string.IsNullOrWhiteSpace(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }

                    val1 = Convert.ToDouble(txtValue.Text);

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                            {
                                if (dVal > val1)
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Less than":
                    if (string.IsNullOrWhiteSpace(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }

                    val1 = Convert.ToDouble(txtValue.Text);

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                            {
                                if (dVal < val1)
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Greater than or equal to":
                    if (string.IsNullOrWhiteSpace(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }

                    val1 = Convert.ToDouble(txtValue.Text);

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                            {
                                if (dVal >= val1)
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Less than or equal to":
                    if (string.IsNullOrWhiteSpace(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }

                    val1 = Convert.ToDouble(txtValue.Text);

                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && double.TryParse(Convert.ToString(oRng[i, j]), out dVal))
                            {
                                if (dVal <= val1)
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Logical TRUE":                                       
                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && bool.TryParse(Convert.ToString(oRng[i, j]),out isBool))
                            {
                                if (bool.Parse(Convert.ToString(oRng[i, j])))
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Logical FALSE":                                        
                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrWhiteSpace(Convert.ToString(oRng[i, j])) && bool.TryParse(Convert.ToString(oRng[i, j]),out isBool))
                            {
                                if (!bool.Parse(Convert.ToString(oRng[i, j])))
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Similar to pattern":
                    if (string.IsNullOrEmpty(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }
                    
                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrEmpty(Convert.ToString(oRng[i, j])))
                            {
                                if (IsLike(txtValue.Text,oRng[i,j].ToString()))
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                case "Not similar to pattern":
                    if (string.IsNullOrEmpty(txtValue.Text))
                    {                        
                        MessageBox.Show("Please enter the value");
                        this.Activate();
                        return;
                    }
                    
                    oRng = rng.Value2;
                    sAddress = "";
                    for(int i = 1;i<=oRng.GetLength(0);i++)
                    {
                        for(int j = 1;j<=oRng.GetLength(1);j++)
                        {
                            if (!string.IsNullOrEmpty(Convert.ToString(oRng[i, j])))
                            {
                                if (!IsLike(txtValue.Text,oRng[i,j].ToString()))
                                    sAddress = sAddress + rng.Cells[i, j].Address + " ";
                            }
                        }
                    } 
                    break;

                default: break;                    
            }
            sAddress = Globals.ThisAddIn.Application.WorksheetFunction.Trim(sAddress);
            
            sAddress = sAddress.Replace(" ", ",").Replace("$","");

            if ((new Regex(@",")).Matches(sAddress).Count > 0 || sAddress.Length > 0)
            {
                sCombineAdd = sAddress.Split(',');
                sAddress = sCombineAdd[0];

                foreach (string str in sCombineAdd)
                {                                        
                    sAddress = Globals.ThisAddIn.Application.Union(Globals.ThisAddIn.Application.ActiveSheet.Range(sAddress), Globals.ThisAddIn.Application.ActiveSheet.Range(str)).Address;
                }

                SelectCellByAddress(sAddress, bSelectCells);
            }
            else
                MessageBox.Show("This value is not found");

            this.Close();
        }

        static public bool IsLike(string sPattern,string sString)
        {   
            Regex regex = new Regex(sPattern,RegexOptions.IgnoreCase );
            return regex.IsMatch(sString);
        }

        public void SelectCellByAddress(string sAddress, bool bSelectCells)
        {            
            Excel.Range rng = Globals.ThisAddIn.Application.ActiveSheet.Range(sAddress);            
            
            if (bSelectCells)
                rng.Select();
            else
                rng.EntireRow.Select();
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void comValues_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch(comValues.Text)
            {
                case "Between":
                case "Not Between": txtFirstValue.Visible = txtSecondValue.Visible = lblAnd.Visible = true;
                    txtValue.Visible = lblValue.Visible = false;
                    break;
                case "Equal to":
                case "Not equal to":                    
                case "Greater than":                    
                case "Less than":                    
                case "Greater than or equal to":                    
                case "Less than or equal to":                    
                case "Similar to pattern":
                case "Not similar to pattern": txtValue.Visible = lblValue.Visible = true; 
                    txtFirstValue.Visible = txtSecondValue.Visible = lblAnd.Visible = false;                    
                    break;
                case "Logical TRUE":
                case "Logical FALSE":
                case "Empty":
                default: txtFirstValue.Visible = txtSecondValue.Visible = lblAnd.Visible = txtValue.Visible = lblValue.Visible = false;
                    break;
            }
        }
    }
}
